package org.jeecg.modules.demo.crm.util.execl;


import org.jeecg.common.util.CommonUtils;
import org.jeecg.modules.demo.crm.entity.CTask;
import org.jeecg.modules.demo.crm.util.wx.WxAvatar2MultipartFile;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Component;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.exception.JeecgBootException;
import org.springframework.web.multipart.MultipartFile;
import java.nio.file.Files;

import java.nio.file.Paths;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelWriterUtils {

    // 1cm = 28.35 points，这是Excel中高度的单位
    private static final float CM_TO_POINTS = 28.35f;
    // 1列宽单位 ≈ 0.145 cm，这是用于计算列宽的经验值
    private static final float CM_TO_EXCEL_COLUMN_WIDTH = 0.145f;
    // 1点 (point) = 9525 EMUs (English Metric Units)，用于精确设置图片位置
    private static final int EMUS_PER_POINT = 9525;

    private static  Map<String,String> url = new HashMap<>();
    // 静态代码块
    static {
        // 在类加载时初始化url
        url.put("张兴志", "http://154.201.66.67:9000/blithe-crm/temp/excel/工程服务验收单-模版-张兴志.xlsx");
        url.put("詹继锋", "http://154.201.66.67:9000/blithe-crm/temp/excel/工程服务验收单-模版-詹继锋.xlsx");
    }
    public static void writer(String serviceUnit,
                              String serviceProject,
                              String projectNo,
                              String date,
                              List<String> imagePaths,
                              String serviceContentText, String contactPerson, CTask cTask){

      try {
          // 获取项目根目录
          String projectRoot = System.getProperty("user.dir");

          // 获取当前时间戳
          SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
          String timestamp = sdf.format(new Date());

          // 设置输出文件路径（带时间戳）
          String outputPath = projectRoot + "/验收单_"+serviceProject+"_"+projectNo+"_ "+ timestamp +".xlsx";
          //String outputPdfPath = projectRoot + "/验收单_"+serviceProject+"_"+projectNo+"_ "+ timestamp +".pdf";

          // 获取 模板位置
          String templateUrl = url.get(contactPerson);
          InputStream templateInputStream = downloadTemplateFromUrl(templateUrl);
          XSSFWorkbook workbook = new XSSFWorkbook(templateInputStream);
          Sheet sheet = workbook.getSheetAt(0);


          // 设置字体为 SimSun，字号为 11
          Font font = workbook.createFont();
          font.setFontName("SimSun");
          font.setFontHeightInPoints((short) 11);

          // 创建单元格样式，包括字体、垂直居中和边框
          CellStyle cellStyle = workbook.createCellStyle();
          cellStyle.setFont(font);
          cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
          cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
          cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
          cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
          cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
          cellStyle.setBorderRight(BorderStyle.THIN); // 右边框

          // 写入文字
          sheet.getRow(1).getCell(1).setCellValue(serviceUnit);    // B2
          sheet.getRow(2).getCell(1).setCellValue(serviceProject); // B3
          sheet.getRow(3).getCell(1).setCellValue(projectNo);      // B4
          sheet.getRow(4).getCell(1).setCellValue(serviceContentText);

          String serverText = "服务单位" +
                  "\n" +
                  "\n" +
                  "\n                  "+date;

          sheet.getRow(7).getCell(3).setCellValue(serverText);           // D9


          int perRow = 4;
          float widthCm = 7f;
          float heightCm= 7f;
          if(imagePaths.size()> 12){
              throw new JeecgBootException("文件超过12个，请手动处理");
          }
          // 插入图片（从B5开始，每行4张，4cm×4cm）
          // 这里调用的insertImagesExact方法会处理行高和列宽的设置
          insertImagesExact(sheet, imagePaths, 5, 1, workbook, perRow, widthCm, heightCm);
          templateInputStream.close();

          FileOutputStream fos = new FileOutputStream(outputPath);
          workbook.write(fos);
          fos.close();
          workbook.close();


          MultipartFile multipartExcelFile = WxAvatar2MultipartFile.convertLocal(outputPath);
          String excelFile = CommonUtils.upload(multipartExcelFile, "temp/excel", "minio");
          System.out.println("Excel 生成完成：" + excelFile);


        // AsposeCellsUtil.excelToPdf(outputPath, outputPdfPath);
        // MultipartFile multipartPdfFile = WxAvatar2MultipartFile.convertLocal(outputPdfPath);
        // String pdfFile = CommonUtils.upload(multipartPdfFile, "temp/excel", "minio");
        // System.out.println("pdf 生成完成：" + pdfFile);

          // 删除本地文件
          Files.delete(Paths.get(outputPath));
        //  Files.delete(Paths.get(outputPdfPath));

          cTask.setExeclUrl(excelFile);
        //  cTask.setPdfUrl(pdfFile);

      } catch (Exception e) {
          e.printStackTrace();
          throw new RuntimeException(e);
      }


    }


    /**
     * 在Excel中插入图片并严格控制尺寸，同时根据图片数量自动调整行高
     * @param sheet Excel工作表对象
     * @param imagePaths 图片路径列表
     * @param startRow 起始行（Excel索引，从0开始，即第6行）
     * @param startCol 起始列（Excel索引，从0开始，即B列）
     * @param workbook 工作簿对象
     * @param perRow 每行显示的图片数量
     * @param widthCm 图片宽度（单位：厘米）
     * @param heightCm 图片高度（单位：厘米）
     */
    private static void insertImagesExact(Sheet sheet, List<String> imagePaths, int startRow, int startCol,
                                          XSSFWorkbook workbook, int perRow, float widthCm, float heightCm) throws IOException {
        XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();

        // 高度计算：将厘米转换为Excel的高度单位（磅）
        float rowHeightPoints = CM_TO_POINTS * heightCm;
        // 宽度计算：将厘米转换为Excel的列宽单位（1/256个字符宽度）
        int excelColWidthUnits = (int) (widthCm / CM_TO_EXCEL_COLUMN_WIDTH);

        // 设置对应列宽
        for (int i = 0; i < perRow; i++) {
            sheet.setColumnWidth(startCol + i, excelColWidthUnits * 256);
        }

        // 计算所有图片所需的总行数，用于设置第6行的总高度
        int totalImageRows = (int) Math.ceil(imagePaths.size() / (double) perRow);
        float totalHeightPoints = totalImageRows * rowHeightPoints;

        // 设置起始行（第6行）的高度
        Row excelRow = sheet.getRow(startRow);
        if (excelRow == null) {
            excelRow = sheet.createRow(startRow);
        }
        excelRow.setHeightInPoints(totalHeightPoints);

        int colOffset = 0;
        int rowOffset = 0;

        for (String imageUrl : imagePaths) {
         /*   InputStream is = new FileInputStream(imgPath);
            byte[] bytes = readAllBytes(is);
            is.close();*/

            byte[] imageBytes = downloadImage(imageUrl);


            int pictureIdx;
            if (imageUrl.toLowerCase().endsWith(".png")) {
                pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
            } else {
                pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
            }

            int col = startCol + colOffset;

            // 计算图片在第6行内的垂直偏移量
            int yOffsetEmu = (int) (rowOffset * rowHeightPoints * EMUS_PER_POINT);
            int yEndOffsetEmu = (int) ((rowOffset + 1) * rowHeightPoints * EMUS_PER_POINT);

            // 创建一个客户端锚点，用于定位和调整图片大小
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            // 将所有图片都锚定在同一行 (startRow) 内
            anchor.setCol1(col);
            anchor.setRow1(startRow);
            anchor.setCol2(col + 1);
            anchor.setRow2(startRow);
            // 通过 dy1 和 dy2 精确控制图片在垂直方向上的位置
            anchor.setDy1(yOffsetEmu);
            anchor.setDy2(yEndOffsetEmu);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

            drawing.createPicture(anchor, pictureIdx);

            colOffset++;
            if (colOffset == perRow) { // 如果一行已经满了，就换到“虚拟”的下一行
                colOffset = 0;
                rowOffset++;
            }
        }
    }

    private static byte[] readAllBytes(InputStream is) throws IOException {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        byte[] data = new byte[1024];
        int nRead;
        while ((nRead = is.read(data, 0, data.length)) != -1) {
            buffer.write(data, 0, nRead);
        }
        buffer.flush();
        return buffer.toByteArray();
    }
    /**
     * 从网络下载图片并返回字节数组
     * @param imageUrl 图片的URL
     * @return 图片的字节数组
     * @throws IOException 如果下载过程中发生错误
     */
    private static byte[] downloadImage(String imageUrl) throws IOException {
        URL url = new URL(imageUrl);
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setRequestMethod("GET");
        connection.setConnectTimeout(5000);
        connection.setReadTimeout(5000);

        int responseCode = connection.getResponseCode();
        if (responseCode == HttpURLConnection.HTTP_OK) {
            try (InputStream is = connection.getInputStream();
                 ByteArrayOutputStream buffer = new ByteArrayOutputStream()) {
                byte[] data = new byte[1024];
                int nRead;
                while ((nRead = is.read(data, 0, data.length)) != -1) {
                    buffer.write(data, 0, nRead);
                }
                buffer.flush();
                return buffer.toByteArray();
            }
        } else {
            throw new IOException("Failed to download image. Server responded with code: " + responseCode);
        }
    }

    private static InputStream downloadTemplateFromUrl(String templateUrl) throws IOException {
        URL url = new URL(templateUrl);
        return url.openStream();
    }

}
